package com.JDBC;

import java.io.*;
import java.sql.*;

/**
 * @author jiangbang
 * @date 2016/10/18
 */
public class JDBC01 {
    public static void main(String[] args) {
        test05();
    }

    /**
     * 最基本的数据库操作
     * 不推荐使用Statement，当sql出现问题时，容易引发数据库的各种问题
     * 即sql注入问题
     */
    private static void test01() {
        try {
            //加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //建立连接（连接对象内部其实包含了socket对象，是一个远程的链接，比较耗时，这是Connection对象的一个要点）
            //正式开发中，为了提高效率，都会使用连接池来管理连接对象
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            //执行sql
            Statement stmt = conn.createStatement();
            stmt.execute("insert into demo (name,age,create_date) values ('蒋棒','18',now())");
            conn.close();
            stmt.close();
            //测试Sql注入
//            String id = "2 or 1=1";   用户恶意输入1=1导致所有被删
//            String sql = "delete from demo where id ="+id;
//            stmt.execute(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 使用PreparedStatement操作数据库
     */
    private static void test02() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            String sql = "insert into demo (name,age,create_date) values (?,?,?)"; //? 占位符
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "蒋棒");  //参数索引从1开始计算，而不是0
            ps.setString(2, "18");
            ps.setDate(3, new java.sql.Date(System.currentTimeMillis())); //ps.setObject(2,"18")   使用Object一样可以
            ps.execute();
            conn.close();
            ps.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询功能,conn需要关闭
     */
    private static void test03() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            String sql = "select * from demo where id>?"; //? 占位符
            ps = conn.prepareStatement(sql);
            ps.setObject(1, 1);    //把大于1的查询出来

            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getInt(1) + "--" + rs.getString(2) + "--" + rs.getString(3) + "--" + rs.getDate(4));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //遵循 resultSet -->  statment -->connection这样的关闭顺序，一定要讲三个try分开写
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



    /**
     * 批量处理+事务处理
     */
    private static void test04() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            conn.setAutoCommit(false);  //设为手动提交
            stmt = conn.createStatement();
            for(int i=1;i<11;i++){
                stmt.addBatch("insert into demo (name,age,create_date) values ('批量添加','"+i+"',now())");
            }
            stmt.executeBatch();
            conn.commit();//提交事务
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //遵循 resultSet -->  statment -->connection这样的关闭顺序，一定要讲三个try分开写
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



    /**
     * 测试文本大对象
     */
    private static void test05() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            String sql = "insert into demo (name,text) values (?,?)"; //? 占位符
            ps = conn.prepareStatement(sql);
            ps.setObject(1, "蒋棒");    //把大于1的查询出来
//            ps.setClob(2, new FileReader(new File("D:\\1.txt")));
            //将程序中的字符创输入到数据库的CLOB字段中
            ps.setClob(2, new BufferedReader(new InputStreamReader(new ByteArrayInputStream("asdfasdfasdfasdf".getBytes()))));
            ps.executeUpdate();

//            ps = conn.prepareStatement("select * from demo where id = ?");
//            ps.setObject(1,15);
//            rs = ps.executeQuery();
//            while(rs.next()){
//                Clob c = rs.getClob("text");
//                Reader r = c.getCharacterStream();
//                int temp =0;
//                while((temp = r.read())!=-1){
//                    System.out.println((char)temp);
//                }
//            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //遵循 resultSet -->  statment -->connection这样的关闭顺序，一定要讲三个try分开写

            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
